<?php

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query;

class DefaultDb_Repositories_RoutePointRepository extends EntityRepository
{

    /**
     * Obtenemos el ultimo punto activo de una ruta
     * @param integer $routeId
     * @return integer Id del ultimo punto de la ruta, null si no tiene puntos
     */
    public function getLastPointIdByRoute($routeId)
    {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $query = '
            SELECT point_id
            FROM route_points
            WHERE 
                    route_id = ' . $routeId . '
                AND status = ' . DefaultDb_Entities_RoutePoint::STATUS_NORMAL . '
            ORDER BY order_number DESC';
        $res = $conn->executeQuery($query);
        $res = $res->fetchAll();
        $lastPointId = null;
        if (count($res) > 0)
        {
            $lastPointId = $res[0][0];
        }
        return $lastPointId;
    }

    /**
     * Insertamos un punto de venta / centro de intermcabio en una ruta
     * @param DefaultDb_Entities_Route $route
     * @param DefaultDb_Entities_Point $point
     * @param type $minutes
     * @param type $typePoint
     * @param type $required
     * @return \DefaultDb_Entities_RoutePoint
     */
    public function insert(DefaultDb_Entities_Route $route, DefaultDb_Entities_Point $point, $minutes = 0, $typePoint = DefaultDb_Entities_Point::TYPE_SALE_POINT, $required = DefaultDb_Entities_RoutePoint::REQUIERED)
    {
        $em = $this->getEntityManager();
        $newRecord = new DefaultDb_Entities_RoutePoint;
        $orderNumber = $this->getLastOrderNumberByRoute($route);
        $orderNumber++;
        $arrivalTime = new DateTime();
        $arrivalTime->setTime(0, $minutes, 0);
        $newRecord->setRoute($route);
        $newRecord->setOrder($orderNumber); //obtener el ultimo
        $newRecord->setPoint($point);
        $newRecord->setStatus(DefaultDb_Entities_Point::STATUS_NORMAL);
        $newRecord->setRequired($required);
        $newRecord->setArrivalTime($arrivalTime);
        $em->persist($newRecord);
        $em->flush();
        return $newRecord;
    }

    /**
     * Insertamos un centro de intercambio como punto de una ruta
     * @param DefaultDb_Entities_Route $route
     * @param DefaultDb_Entities_Point $exchangeCenter
     * @param type $minutes
     * @param type $required
     * @return \DefaultDb_Entities_RoutePoint
     */
    public function insertExchangeCenter(DefaultDb_Entities_Route $route, DefaultDb_Entities_Point $exchangeCenter, $minutes = 0, $required = DefaultDb_Entities_RoutePoint::REQUIERED)
    {
        return $this->insert($route, $exchangeCenter, $minutes, DefaultDb_Entities_Point::TYPE_EXCHANGE_CENTER, $required);
    }

    /**
     * Insertamos un punto de venta en la ruta
     * @param DefaultDb_Entities_Route $route
     * @param DefaultDb_Entities_Point $salePoint
     * @param type $minutes
     * @param type $required
     * @return \DefaultDb_Entities_RoutePoint
     */
    public function insertSalePoint(DefaultDb_Entities_Route $route, DefaultDb_Entities_Point $salePoint, $minutes = 0, $required = DefaultDb_Entities_RoutePoint::REQUIERED)
    {
        return $this->insert($route, $salePoint, $minutes, DefaultDb_Entities_Point::TYPE_SALE_POINT, $required);
    }

    /**
     * Checamos si existe un punto en una ruta.
     * @param int $routeId
     * @param int $pointId
     * @return boolean
     */
    public function existPointInRoute($routeId, $pointId)
    {
        $em = $this->getEntityManager();
        $dql = '
                SELECT rp.id
                FROM DefaultDb_Entities_RoutePoint rp 
                WHERE rp.route = ' . $routeId . ' 
                    AND rp.point = ' . $pointId . '
                    AND rp.status != 0  
                ORDER BY rp.order ASC';
        $query = $em->createQuery($dql);
        $routePoints = $query->getResult();
        return count($routePoints) > 0;
    }

    /**
     * Devuelve los puntos de la ruta recibida
     * @param int $routeId
     * @return array
     */
    public function getRoutesPointsByRoute($routeId)
    {
        $em = $this->getEntityManager();
        $dql = '
                SELECT rp.id, p.id as idPoint, p.name, p.code, p.type, rp.arrivalTime, rp.required
                FROM DefaultDb_Entities_RoutePoint rp 
                JOIN rp.point p
                JOIN rp.route r
                WHERE r.id = ' . $routeId . ' 
                AND rp.status != 0  
                ORDER BY rp.order ASC';
        $query = $em->createQuery($dql);
        $routePoints = $query->getResult();
        return $routePoints;
    }

    /**
     * Regresa solo los puntos de intercambio de una ruta
     * @param type $route
     * @return type
     */
    public function getPointExchangeByRoute($route)
    {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $query =
                'SELECT p.idpoint, p.name, rp.order_number
        FROM (
            SELECT id idpoint,name 
            FROM points
            WHERE type = ' . DefaultDb_Entities_Point::TYPE_EXCHANGE_CENTER . '
            AND status = ' . DefaultDb_Entities_Point::STATUS_NORMAL . '
        ) p 
        JOIN ( 
            SELECT route_id, point_id, order_number
            FROM route_points
            WHERE route_id =' . $route->getId() . '
                AND status = ' . DefaultDb_Entities_RoutePoint::STATUS_NORMAL . '
            ) rp
	ON p.idpoint = rp.point_id 
        WHERE rp.route_id =' . $route->getId();
        $res = $conn->executeQuery($query);
        $res = $res->fetchAll();
        return $res;
    }

    public function getRoutesPointsByRouteAndPointType($routeId, $pointType)
    {
        $em = $this->getEntityManager();
        $dql = '
            SELECT rp 
            FROM DefaultDb_Entities_RoutePoint rp 
            JOIN rp.point p
            JOIN rp.route r
            WHERE r.id = ' . $routeId . '
            AND p.type = ' . $pointType . '
            ORDER BY p.name ASC';
        $query = $em->createQuery($dql);
        $routePoints = $query->getResult();
        return $routePoints;
    }

    /**
     * Regresa el orden inicial de una ruta ya que no siempre sera uno
     */
    public function getOrderInitialByRoute($route)
    {

        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $query = '
            SELECT min(rp.order_number) ordernumber 
            FROM route_points rp
            WHERE rp.route_id = ' . $route->getId();
        $res = $conn->executeQuery($query);
        $res = $res->fetch(PDO::FETCH_OBJ);
        return $res;
    }

    public function getLastOrderNumberByRoute($route)
    {
        $orderNumber = 0;
        $criteria = array('route' => $route);
        $orderBy = array('order' => 'DESC');
        $limit = 1;
        $routePoints = $this->findBy($criteria, $orderBy, $limit);
        if (is_array($routePoints) == true && count($routePoints) > 0)
            $orderNumber = $routePoints[0]->getOrder();
        return $orderNumber;
    }

    /**
     *
     * @param integer $routeId
     * @param integer $orderNumber
     * @return DefaultDb_Entities_RoutePoint
     */
    public function getStepUpRoutePoint($routeId, $orderNumber)
    {
        $stepUpRoutePoint = false;
        $em = $this->getEntityManager();
        $dql = '
            SELECT rp 
            FROM DefaultDb_Entities_RoutePoint rp 
            JOIN rp.route r
            WHERE r.id = ' . $routeId . '
            AND rp.order < ' . $orderNumber . '
            ORDER BY rp.order DESC';
        $query = $em->createQuery($dql);
        $query->setMaxResults(1);
        $routePoints = $query->getResult();
        if (is_array($routePoints) == true && count($routePoints) > 0)
            $stepUpRoutePoint = $routePoints[0];
        return $stepUpRoutePoint;
    }

    /**
     *
     * @param integer $routeId
     * @param integer $orderNumber
     * @return DefaultDb_Entities_RoutePoint
     */
    public function getStepDownRoutePoint($routeId, $orderNumber)
    {
        $stepUpRoutePoint = false;
        $em = $this->getEntityManager();
        $dql = '
            SELECT rp 
            FROM DefaultDb_Entities_RoutePoint rp 
            JOIN rp.route r
            WHERE r.id = ' . $routeId . '
            AND rp.order > ' . $orderNumber . '
            ORDER BY rp.order ASC';
        $query = $em->createQuery($dql);
        $query->setMaxResults(1);
        $routePoints = $query->getResult();
        if (is_array($routePoints) == true && count($routePoints) > 0)
            $stepUpRoutePoint = $routePoints[0];
        return $stepUpRoutePoint;
    }

    public function getRoutesByPoint($point)
    {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $query = '
            SELECT DISTINCT route_id from route_points where point_id=' . $point->getId() . ' AND status = ' . DefaultDb_Entities_Route::STATUS_ACTIVE;
        $res = $conn->executeQuery($query);
        $res = $res->fetchAll();
        return $res;
    }

    /**
     * Buscamos los registros que coincidan con el punto, pero dandole preferencia
     * a que la ruta que nos lleve a ese punto sea la misma ruta desde la que se
     * parte.
     * Ejemplo: Busco una ruta para llegar al punto C y la ruta que lo recoge es
     * la ruta 2.
     * 
     * Ruta 1: A B C D E
     * Ruta 2: Z C D E F
     * Ruta 3: K L M N O
     * 
     * La ruta 3 se descarta porque ni siquiera pasa por el punto.
     * La ruta 1 y 2 si pasan por el punto C, pero ya que la ruta actual es la ruta
     * 2 (porque en ella se recogio el paquete) entonces tomamos la ruta 2 ya que
     * de esta forma la entrega se hace sobre la misma ruta, sin necesidad de 
     * hacer cambios entre rutas.
     * 
     * @author Nylaye
     * @param DefaultDb_Entities_Point $point
     * @param DefaultDb_Entities_Route $route
     * @return integer Id route_points
     */
    public function getRouteByPointAndRoute($point, $route)
    {
        $pointId = $point->getId();
        $routeId = $route->getId();
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $query = '
            SELECT rp.route_id
            FROM route_points rp
            INNER JOIN routes r ON rp.route_id = r.id
            INNER JOIN points p ON rp.point_id = p.id
            WHERE 
                        point_id = ' . $pointId . ' 
                    AND rp.status = ' . DefaultDb_Entities_RoutePoint::STATUS_NORMAL . ' 
                    AND r.status = ' . DefaultDb_Entities_Route::STATUS_ACTIVE . '
            ORDER BY route_id = ' . $routeId . ' DESC';
        $res = $conn->executeQuery($query);
        $res = $res->fetchAll();
        return count($res) > 0 ? (int) $res[0]['route_id'] : false;
    }

    /**
     * Checamos que un punto p1 de una ruta r1 este agendado antes que el punto
     * p2 que pertenece a la misma ruta r1.
     * @param integer $routeId
     * @param integer $pointSourceId
     * @param integer $pointDestinyId
     * @return bool true si p1 se encuentra antes que p2, false en cualquier otro
     * caso.
     */
    public function firstPointBeforeSecondPoint($routeId, $pointSourceId, $pointDestinyId)
    {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $query = '
            SELECT rp.order_number < rp2.order_number x
            FROM route_points rp
            INNER JOIN route_points rp2 ON rp2.route_id = ' . $routeId . ' 
                AND rp2.point_id = ' . $pointDestinyId . '
            INNER JOIN points p ON p.id = rp.point_id
            INNER JOIN points p2 ON rp2.point_id = p2.id
            WHERE rp.route_id = ' . $routeId . ' AND rp.point_id = ' . $pointSourceId;
        $res = $conn->executeQuery($query);
        $res = $res->fetchAll();
        return count($res) > 0 ? ((int) $res[0]['x']) == 1 : false;
    }

    public function getSecuencialActivityByDateByRoute($starDate, $route, $dateStartRoute = null)
    {
        $validStatus = array(
            DefaultDb_Entities_M3CommerceOrder::SHIPPING_STATUS_TO_SHIPPED,
            DefaultDb_Entities_M3CommerceOrder::SHIPPING_STATUS_SHIPPED,
            DefaultDb_Entities_M3CommerceOrder::SHIPPING_STATUS_IN_INTERCHANGE_CENTER,
            DefaultDb_Entities_M3CommerceOrder::SHIPPING_STATUS_DELIVERED
        );
        $statusStr = implode(',', $validStatus);
        $date = new DateTime($starDate->format('Y-m-d 00:00:00'));
        $date->modify('+1 day');
        $em = $this->getEntityManager();

        $dql = '
        SELECT rps, seq FROM
        (
            SELECT rp FROM DefaultDb_Entities_RoutePoint rp
            WHERE rp.route =' . $route->getId() . '
            AND rp.status = ' . DefaultDb_Entities_RoutePoint::STATUS_NORMAL . '    
        ) rps
        LEFT JOIN
        (
            SELECT s
            FROM DefaultDb_Entities_SequentialActivities s
            JOIN s.order o
            JOIN s.routePoint rp
            JOIN rp.route r
            JOIN rp.point p
            WHERE r.id =' .
                $route->getId() . ($dateStartRoute === null ?
                        ' AND s.shippingDate >= \'' . $starDate->format("Y-m-d") . '\'
                AND s.shippingDate <= \'' . $date->format("Y-m-d") . '\'' :
                        ' AND s.routeDate=\'' . $dateStartRoute . '\''
                ) .
                ' AND o.shippingStatus IN (' . $statusStr . ')
            ORDER BY s.shippingDate ASC
        ) seq ON rps.id=seq.routePoint_id
        ';
        $dql = '
            SELECT s
            FROM DefaultDb_Entities_SequentialActivities s
            JOIN s.order o
            JOIN s.routePoint rp
            JOIN rp.route r
            JOIN rp.point p
            WHERE r.id =' .
                $route->getId() . ($dateStartRoute === null ?
                        ' AND s.shippingDate >= \'' . $starDate->format("Y-m-d") . '\'
                AND s.shippingDate <= \'' . $date->format("Y-m-d") . '\'' :
                        ' AND s.routeDate=\'' . $dateStartRoute . '\''
                ) .
                ' AND o.shippingStatus IN (' . $statusStr . ')
            ORDER BY s.shippingDate ASC
        ';
        $query = $em->createQuery($dql);
        //echo '<pre>'.$query->getSQL();
        $routePoints = $query->getResult();
        return $routePoints;

//        $query = $em->createQuery($dql);
//        echo '<pre>'.$query->getSQL();
//        $routePoints = $query->getResult();
//        return $routePoints;
//        
//        $em = $this->getEntityManager();
//        $conn = $em->getConnection();
//        $query = '
//            SELECT s0_.id AS id0, s0_.type AS type1, s0_.shipping_date 
//        AS shipping_date2, s0_.route_date AS route_date3, s0_.routePoint_id 
//        AS routePoint_id4, s0_.order_id AS order_id5 
//        FROM sequential_activities s0_ 
//        INNER JOIN m3_commerce_order m1_ ON s0_.order_id = m1_.id 
//        INNER JOIN route_points r2_ ON s0_.routePoint_id = r2_.id 
//        INNER JOIN routes r3_ ON r2_.route_id = r3_.id 
//        INNER JOIN points p4_ ON r2_.point_id = p4_.id 
//        WHERE r3_.id = 1 AND s0_.route_date = \'2012-11-05 22:00:00\' 
//        AND m1_.shipping_status IN (1, 2) ORDER BY s0_.shipping_date ASC;
//        SELECT * FROM route_points WHERE route_id = 1';
//        $res = $conn->executeQuery($query);
//        $res = $res->fetchAll();
//        return $res;
    }

}